﻿namespace WMS6.SQLServerDAL
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Common;
    using System.Data.SqlClient;
    using WMS6.Common;
    using WMS6.DataEntity;
    using WMS6.DBUtility;
    using WMS6.IDAL;
    using WMS6.QueryEntity;

    public class DeliveryOrderDA : IDeliveryOrderDA
    {
        private string ACTUAL_SHIP_DATE = "@ACTUAL_SHIP_DATE";
        private string BILLING_TYPE = "@BILLING_TYPE";
        private string C_ADDRESS = "@C_ADDRESS";
        private string C_CITY = "@C_CITY";
        private string C_CONTACT1 = "@C_CONTACT1";
        private string C_CONTACT2 = "@C_CONTACT2";
        private string C_COUNTRY = "@C_COUNTRY";
        private string C_EMAIL1 = "@C_EMAIL1";
        private string C_EMAIL2 = "@C_EMAIL2";
        private string C_FAX1 = "@C_FAX1";
        private string C_FAX2 = "@C_FAX2";
        private string C_NAME = "@C_NAME";
        private string C_PROVINCE = "@C_PROVINCE";
        private string C_TEL1 = "@C_TEL1";
        private string C_TEL2 = "@C_TEL2";
        private string C_ZIP = "@C_ZIP";
        private string CARRIER_ID = "@CARRIER_ID";
        private string CREATED_BY = "@CREATED_BY";
        private string CUSTOMER_ID = "@CUSTOMER_ID";
        private string D_ADDRESS = "@D_ADDRESS";
        private string D_CITY = "@D_CITY";
        private string D_CONTACT = "@D_CONTACT";
        private string D_COUNTRY = "@D_COUNTRY";
        private string D_EMAIL = "@D_EMAIL";
        private string D_FAX = "@D_FAX";
        private string D_IS_WEEKED_WORK = "@D_IS_WEEKED_WORK";
        private string D_PROVINCE = "@D_PROVINCE";
        private string D_TEL = "@D_TEL";
        private string D_ZIP = "@D_ZIP";
        private string DELIVERY_LOC_ID = "@DELIVERY_LOC_ID";
        private string DELIVERY_ORDER_ID = "@DELIVERY_ORDER_ID";
        private string DELIVERY_ORDER_TYPE = "@DELIVERY_ORDER_TYPE";
        private string DEPARTURE = "@DEPARTURE";
        private string DESTINATION = "@DESTINATION";
        private string END_LOC_ADDR = "@END_LOC_ADDR";
        private string EXPECT_ARRIVAL_DATE = "@EXPECT_ARRIVAL_DATE";
        private string EXTERN_ORDER_ID = "@EXTERN_ORDER_ID";
        private string EXTERN_ORDER_ID2 = "@EXTERN_ORDER_ID2";
        private string IS_ACTIVE = "@IS_ACTIVE";
        private string OWNER_ID = "@OWNER_ID";
        private string P_ADDRESS = "@P_ADDRESS";
        private string P_CITY = "@P_CITY";
        private string P_CONTACT = "@P_CONTACT";
        private string P_COUNTRY = "@P_COUNTRY";
        private string P_EMAIL = "@P_EMAIL";
        private string P_FAX = "@P_FAX";
        private string P_IS_WEEKED_WORK = "@P_IS_WEEKED_WORK";
        private string P_PROVINCE = "@P_PROVINCE";
        private string P_TEL = "@P_TEL";
        private string P_ZIP = "@P_ZIP";
        private string PAYMENT_TYPE = "@PAYMENT_TYPE";
        private string PICKUP_LOC_ID = "@PICKUP_LOC_ID";
        private string PICKUP_TYPE = "@PICKUP_TYPE";
        private string PRIORITY = "@PRIORITY";
        private string REMARK = "@REMARK";
        private string REQUESTED_SHIP_DATE = "@REQUESTED_SHIP_DATE";
        private string SQL_CHECK_DELIVERY_ORDER_ID_UNIQUE = " SELECT COUNT(1) FROM @_@DELIVERY_ORDER WHERE DELIVERY_ORDER_ID = @DELIVERY_ORDER_ID ";
        private string SQL_DELETE_DELIVERY_ORDER = " DELETE FROM @_@DELIVERY_ORDER WHERE DELIVERY_ORDER_ID = @DELIVERY_ORDER_ID ";
        private string SQL_INSERT_DELIVERY_ORDER = " INSERT INTO @_@DELIVERY_ORDER ( WH_ID, DELIVERY_ORDER_ID, EXTERN_ORDER_ID, EXTERN_ORDER_ID2, OWNER_ID, DELIVERY_ORDER_TYPE, START_LOC_ADDR, END_LOC_ADDR, DEPARTURE, DESTINATION, TOTAL_QTY, TOTAL_WEIGHT, TOTAL_CUBE, REQUESTED_SHIP_DATE, ACTUAL_SHIP_DATE, EXPECT_ARRIVAL_DATE, STATUS, TRANSFER_TYPE, PICKUP_TYPE, PAYMENT_TYPE, BILLING_TYPE, CARRIER_ID, CUSTOMER_ID, C_NAME, C_CITY, C_PROVINCE, C_COUNTRY, C_ZIP, C_CONTACT1, C_TEL1, C_FAX1, C_EMAIL1, C_CONTACT2, C_TEL2, C_FAX2, C_EMAIL2, C_ADDRESS, PICKUP_LOC_ID, P_ADDRESS, P_COUNTRY, P_PROVINCE, P_CITY, P_CONTACT, P_TEL, P_ZIP, P_FAX, P_EMAIL, P_IS_WEEKED_WORK, DELIVERY_LOC_ID, D_ADDRESS, D_COUNTRY, D_PROVINCE, D_CITY, D_CONTACT, D_TEL, D_ZIP, D_FAX, D_EMAIL, D_IS_WEEKED_WORK, PRIORITY, UDF1, UDF2, UDF3, UDF4, UDF5, UDF6, UDF7, UDF8, UDF9, UDF10, IS_ACTIVE, REMARK, CREATED_BY, CREATED_DATE, UPDATED_BY, UPDATED_DATE) VALUES ( @WH_ID, @DELIVERY_ORDER_ID, @EXTERN_ORDER_ID, @EXTERN_ORDER_ID2, @OWNER_ID, @DELIVERY_ORDER_TYPE, @START_LOC_ADDR, @END_LOC_ADDR, @DEPARTURE, @DESTINATION, @TOTAL_QTY, @TOTAL_WEIGHT, @TOTAL_CUBE, @REQUESTED_SHIP_DATE, @ACTUAL_SHIP_DATE, @EXPECT_ARRIVAL_DATE, @STATUS, @TRANSFER_TYPE, @PICKUP_TYPE, @PAYMENT_TYPE, @BILLING_TYPE, @CARRIER_ID, @CUSTOMER_ID, @C_NAME, @C_CITY, @C_PROVINCE, @C_COUNTRY, @C_ZIP, @C_CONTACT1, @C_TEL1, @C_FAX1, @C_EMAIL1, @C_CONTACT2, @C_TEL2, @C_FAX2, @C_EMAIL2, @C_ADDRESS, @PICKUP_LOC_ID, @P_ADDRESS, @P_COUNTRY, @P_PROVINCE, @P_CITY, @P_CONTACT, @P_TEL, @P_ZIP, @P_FAX, @P_EMAIL, @P_IS_WEEKED_WORK, @DELIVERY_LOC_ID, @D_ADDRESS, @D_COUNTRY, @D_PROVINCE, @D_CITY, @D_CONTACT, @D_TEL, @D_ZIP, @D_FAX, @D_EMAIL, @D_IS_WEEKED_WORK, @PRIORITY, @UDF1, @UDF2, @UDF3, @UDF4, @UDF5, @UDF6, @UDF7, @UDF8, @UDF9, @UDF10, @IS_ACTIVE, @REMARK, @CREATED_BY, GETDATE(), @UPDATED_BY, GETDATE() )  ";
        private string SQL_SELECT_ALL_DELIVERY_ORDER = "SELECT    DO.ROWID, DO.WH_ID, DO.DELIVERY_ORDER_ID, DO.EXTERN_ORDER_ID, DO.EXTERN_ORDER_ID2, DO.OWNER_ID,  DO.DELIVERY_ORDER_TYPE as DELIVERY_ORDER_TYPE,   T.[TEXT] AS DO_DELIVERY_ORDER_TYPE,  DO.START_LOC_ADDR, DO.END_LOC_ADDR,DO.DEPARTURE, DO.DESTINATION, DO.TOTAL_QTY, DO.TOTAL_WEIGHT, DO.TOTAL_CUBE, DO.REQUESTED_SHIP_DATE, DO.ACTUAL_SHIP_DATE, DO.EXPECT_ARRIVAL_DATE,   DO.STATUS as STATUS,  T1.[TEXT] as DO_STATUS,  DO.TRANSFER_TYPE as TRANSFER_TYPE,   T2.[TEXT] as DO_TRANSFER_TYPE,  DO.PICKUP_TYPE as PICKUP_TYPE,   T3.[TEXT] as DO_PICKUP_TYPE,  DO.PAYMENT_TYPE as PAYMENT_TYPE,   T4.[TEXT] as DO_PAYMENT_TYPE,  DO.BILLING_TYPE as BILLING_TYPE,   T5.[TEXT] as DO_BILLING_TYPE,  DO.CARRIER_ID, DO.CUSTOMER_ID,  DO.C_NAME, DO.C_CITY, DO.C_PROVINCE, DO.C_COUNTRY,DO.C_ZIP, DO.C_CONTACT1, DO.C_TEL1, DO.C_FAX1, DO.C_EMAIL1, DO.C_CONTACT2, DO.C_TEL2, DO.C_FAX2,   DO.C_EMAIL2, DO.C_ADDRESS, DO.PICKUP_LOC_ID, DO.P_ADDRESS, DO.P_COUNTRY, DO.P_PROVINCE, DO.P_CITY, DO.P_CONTACT, DO.P_TEL, DO.P_ZIP, DO.P_FAX,   DO.P_EMAIL, DO.P_IS_WEEKED_WORK, DO.DELIVERY_LOC_ID, DO.D_ADDRESS, DO.D_COUNTRY, DO.D_PROVINCE, DO.D_CITY,   DO.D_CONTACT, DO.D_TEL, DO.D_ZIP, DO.D_FAX, DO.D_EMAIL, DO.D_IS_WEEKED_WORK, DO.PRIORITY, DO.UDF1,   DO.UDF2, DO.UDF3, DO.UDF4, DO.UDF5,DO.UDF6, DO.UDF7, DO.UDF8, DO.UDF9, DO.UDF10, DO.IS_ACTIVE, DO.REMARK, DO.TS,   DO.CREATED_BY, DO.CREATED_DATE, DO.UPDATED_BY,   DO.UPDATED_DATE  FROM         @_@DELIVERY_ORDER as DO  LEFT JOIN @_@SYS_CODE_DETAIL SCD ON SCD.SYS_CODE = DO.DELIVERY_ORDER_TYPE AND SCD.SYS_CODE_TYPE = 'DeliveryType'  LEFT JOIN @_@SYS_CODE_DETAIL SCD1 ON SCD1.SYS_CODE = DO.STATUS AND SCD.SYS_CODE_TYPE = 'DeliveryOrderStatus'  LEFT JOIN @_@SYS_CODE_DETAIL SCD2 ON SCD2.SYS_CODE = DO.TRANSFER_TYPE AND SCD.SYS_CODE_TYPE = 'TransportMode'  LEFT JOIN @_@SYS_CODE_DETAIL SCD3 ON SCD3.SYS_CODE = DO.PICKUP_TYPE AND SCD.SYS_CODE_TYPE = 'PickupType'  LEFT JOIN @_@SYS_CODE_DETAIL SCD4 ON SCD4.SYS_CODE = DO.PAYMENT_TYPE AND SCD.SYS_CODE_TYPE = 'PaymentType'   LEFT JOIN @_@SYS_CODE_DETAIL SCD5 ON SCD5.SYS_CODE = DO.BILLING_TYPE AND SCD.SYS_CODE_TYPE = 'BillingType'  LEFT JOIN sysadm.TRANSLATION T ON 'DeliveryType.'+DO.DELIVERY_ORDER_TYPE = T.ITEM_ID        AND  T.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T.LANG+'-'+T.COUNTRY = @LANGUAGE  LEFT JOIN sysadm.TRANSLATION T1 ON 'DeliveryOrderStatus.'+DO.STATUS = T1.ITEM_ID        AND  T1.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T1.LANG+'-'+T1.COUNTRY = @LANGUAGE  LEFT JOIN sysadm.TRANSLATION T2 ON 'TransportMode.'+DO.TRANSFER_TYPE = T2.ITEM_ID        AND  T2.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T2.LANG+'-'+T2.COUNTRY = @LANGUAGE  LEFT JOIN sysadm.TRANSLATION T3 ON 'PickupType.'+DO.PICKUP_TYPE = T3.ITEM_ID        AND  T3.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T3.LANG+'-'+T3.COUNTRY = @LANGUAGE  LEFT JOIN sysadm.TRANSLATION T4 ON 'PaymentType.'+DO.PAYMENT_TYPE = T4.ITEM_ID        AND  T4.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T4.LANG+'-'+T4.COUNTRY = @LANGUAGE  LEFT JOIN sysadm.TRANSLATION T5 ON 'BillingType.'+DO.BILLING_TYPE = T5.ITEM_ID      AND  T5.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T5.LANG+'-'+T5.COUNTRY = @LANGUAGE INNER JOIN SYSADM.USER_OWNER UO ON DO.OWNER_ID = UO.OWNER_ID AND UO.[USER_ID] = @USER_ID ";
        private string SQL_UPDATE_DELIVERY_ORDER = " UPDATE @_@DELIVERY_ORDER SET WH_ID = @WH_ID, EXTERN_ORDER_ID = @EXTERN_ORDER_ID, EXTERN_ORDER_ID2 = @EXTERN_ORDER_ID2, OWNER_ID = @OWNER_ID, DELIVERY_ORDER_TYPE = @DELIVERY_ORDER_TYPE, START_LOC_ADDR = @START_LOC_ADDR, END_LOC_ADDR = @END_LOC_ADDR, DEPARTURE = @DEPARTURE, DESTINATION = @DESTINATION, TOTAL_QTY = @TOTAL_QTY, TOTAL_WEIGHT = @TOTAL_WEIGHT, TOTAL_CUBE = @TOTAL_CUBE, REQUESTED_SHIP_DATE = @REQUESTED_SHIP_DATE, ACTUAL_SHIP_DATE = @ACTUAL_SHIP_DATE, EXPECT_ARRIVAL_DATE = @EXPECT_ARRIVAL_DATE, STATUS = @STATUS, TRANSFER_TYPE = @TRANSFER_TYPE, PICKUP_TYPE = @PICKUP_TYPE, PAYMENT_TYPE = @PAYMENT_TYPE, BILLING_TYPE = @BILLING_TYPE, CARRIER_ID = @CARRIER_ID, CUSTOMER_ID = @CUSTOMER_ID, C_NAME = @C_NAME, C_CITY = @C_CITY, C_PROVINCE = @C_PROVINCE, C_COUNTRY = @C_COUNTRY, C_ZIP = @C_ZIP, C_CONTACT1 = @C_CONTACT1, C_TEL1 = @C_TEL1, C_FAX1 = @C_FAX1, C_EMAIL1 = @C_EMAIL1, C_CONTACT2 = @C_CONTACT2, C_TEL2 = @C_TEL2, C_FAX2 = @C_FAX2, C_EMAIL2 = @C_EMAIL2, C_ADDRESS = @C_ADDRESS, PICKUP_LOC_ID = @PICKUP_LOC_ID, P_ADDRESS = @P_ADDRESS, P_COUNTRY = @P_COUNTRY, P_PROVINCE = @P_PROVINCE, P_CITY = @P_CITY, P_CONTACT = @P_CONTACT, P_TEL = @P_TEL, P_ZIP = @P_ZIP, P_FAX = @P_FAX, P_EMAIL = @P_EMAIL, P_IS_WEEKED_WORK = @P_IS_WEEKED_WORK, DELIVERY_LOC_ID = @DELIVERY_LOC_ID, D_ADDRESS = @D_ADDRESS, D_COUNTRY = @D_COUNTRY, D_PROVINCE = @D_PROVINCE, D_CITY = @D_CITY, D_CONTACT = @D_CONTACT, D_TEL = @D_TEL, D_ZIP = @D_ZIP, D_FAX = @D_FAX, D_EMAIL = @D_EMAIL, D_IS_WEEKED_WORK = @D_IS_WEEKED_WORK, PRIORITY = @PRIORITY, UDF1 = @UDF1, UDF2 = @UDF2, UDF3 = @UDF3, UDF4 = @UDF4, UDF5 = @UDF5, UDF6 = @UDF6, UDF7 = @UDF7, UDF8 = @UDF8, UDF9 = @UDF9, UDF10 = @UDF10, IS_ACTIVE = @IS_ACTIVE, REMARK = @REMARK, UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE() WHERE DELIVERY_ORDER_ID = @DELIVERY_ORDER_ID  ";
        private string START_LOC_ADDR = "@START_LOC_ADDR";
        private string STATUS = "@STATUS";
        private string TOTAL_CUBE = "@TOTAL_CUBE";
        private string TOTAL_QTY = "@TOTAL_QTY";
        private string TOTAL_WEIGHT = "@TOTAL_WEIGHT";
        private string TRANSFER_TYPE = "@TRANSFER_TYPE";
        private string TS = "@TS";
        private string UDF1 = "@UDF1";
        private string UDF10 = "@UDF10";
        private string UDF2 = "@UDF2";
        private string UDF3 = "@UDF3";
        private string UDF4 = "@UDF4";
        private string UDF5 = "@UDF5";
        private string UDF6 = "@UDF6";
        private string UDF7 = "@UDF7";
        private string UDF8 = "@UDF8";
        private string UDF9 = "@UDF9";
        private string UPDATED_BY = "@UPDATED_BY";
        private string WH_ID = "@WH_ID";

        public bool CheckDeliveryOrderIDUnique(string deliveryOrderID, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@DELIVERY_ORDER_ID", deliveryOrderID) };
            return (DBHelper.ExecuteScalar(CommandType.Text, this.SQL_CHECK_DELIVERY_ORDER_ID_UNIQUE.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public bool CheckDeliveryOrderIDUnique(DataBase dataBase, DbTransaction tran, string deliveryOrderID, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@DELIVERY_ORDER_ID", deliveryOrderID) };
            return (dataBase.ExecuteScalar(tran, CommandType.Text, this.SQL_CHECK_DELIVERY_ORDER_ID_UNIQUE.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public int DeleteDeliveryOrder(List<string> deliveryOrderID, List<string> whLoginID)
        {
            int result = 0;
            DataBase dataBase = new DataBase();
            DbConnection conn = dataBase.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    for (int j = 0; j < deliveryOrderID.Count; j++)
                    {
                        SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@DELIVERY_ORDER_ID", deliveryOrderID[j]) };
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_DELIVERY_ORDER.Replace("@_@", whLoginID[i]), paras);
                    }
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int DeleteDeliveryOrder(DataBase dataBase, DbTransaction tran, List<string> deliveryOrderID, List<string> whLoginID)
        {
            int result = 0;
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    for (int j = 0; j < deliveryOrderID.Count; j++)
                    {
                        SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@DELIVERY_ORDER_ID", deliveryOrderID[j]) };
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_DELIVERY_ORDER.Replace("@_@", whLoginID[i]), paras);
                    }
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public DataSet GetAllDeliveryOrder(string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_DELIVERY_ORDER.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            return DBHelper.ExecuteDataSet(CommandType.Text, sql, paras);
        }

        public DataSet GetAllDeliveryOrder(DataBase dataBase, DbTransaction tran, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_DELIVERY_ORDER.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            return dataBase.ExecuteDataSet(tran, CommandType.Text, sql, paras);
        }

        public DeliveryOrderInfo GetDeliveryOrderByID(string deliveryorderID, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_DELIVERY_ORDER.Replace("@_@", whLoginID) + " WHERE DELIVERY_ORDER_ID = @DELIVERY_ORDER_ID  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@DELIVERY_ORDER_ID", deliveryorderID), new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            DeliveryOrderInfo deliveryOrderInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                if (reader.Read())
                {
                    DateTime CS00003;
                    deliveryOrderInfo = new DeliveryOrderInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["DELIVERY_ORDER_ID"].ToString(), reader["EXTERN_ORDER_ID"].ToString(), reader["EXTERN_ORDER_ID2"].ToString(), reader["OWNER_ID"].ToString(), reader["DELIVERY_ORDER_TYPE"].ToString(), reader["START_LOC_ADDR"].ToString(), reader["END_LOC_ADDR"].ToString(), reader["DEPARTURE"].ToString(), reader["DESTINATION"].ToString(), (reader["TOTAL_QTY"].ToString() != "") ? decimal.Parse(reader["TOTAL_QTY"].ToString()) : 0M, (reader["TOTAL_WEIGHT"].ToString() != "") ? decimal.Parse(reader["TOTAL_WEIGHT"].ToString()) : 0M, (reader["TOTAL_CUBE"].ToString() != "") ? decimal.Parse(reader["TOTAL_CUBE"].ToString()) : 0M, (reader["REQUESTED_SHIP_DATE"].ToString() != "") ? DateTime.Parse(reader["REQUESTED_SHIP_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["ACTUAL_SHIP_DATE"].ToString() != "") ? DateTime.Parse(reader["ACTUAL_SHIP_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["EXPECT_ARRIVAL_DATE"].ToString() != "") ? DateTime.Parse(reader["EXPECT_ARRIVAL_DATE"].ToString()) : (CS00003 = new DateTime()), reader["STATUS"].ToString(), reader["TRANSFER_TYPE"].ToString(), reader["PICKUP_TYPE"].ToString(), reader["PAYMENT_TYPE"].ToString(), reader["BILLING_TYPE"].ToString(), reader["CARRIER_ID"].ToString(), reader["CUSTOMER_ID"].ToString(), reader["C_NAME"].ToString(), reader["C_CITY"].ToString(), reader["C_PROVINCE"].ToString(), reader["C_COUNTRY"].ToString(), reader["C_ZIP"].ToString(), reader["C_CONTACT1"].ToString(), reader["C_TEL1"].ToString(), reader["C_FAX1"].ToString(), reader["C_EMAIL1"].ToString(), reader["C_CONTACT2"].ToString(), reader["C_TEL2"].ToString(), reader["C_FAX2"].ToString(), reader["C_EMAIL2"].ToString(), reader["C_ADDRESS"].ToString(), reader["PICKUP_LOC_ID"].ToString(), reader["P_ADDRESS"].ToString(), reader["P_COUNTRY"].ToString(), reader["P_PROVINCE"].ToString(), reader["P_CITY"].ToString(), reader["P_CONTACT"].ToString(), reader["P_TEL"].ToString(), reader["P_ZIP"].ToString(), reader["P_FAX"].ToString(), reader["P_EMAIL"].ToString(), reader["P_IS_WEEKED_WORK"].ToString(), reader["DELIVERY_LOC_ID"].ToString(), reader["D_ADDRESS"].ToString(), reader["D_COUNTRY"].ToString(), reader["D_PROVINCE"].ToString(), reader["D_CITY"].ToString(), reader["D_CONTACT"].ToString(), reader["D_TEL"].ToString(), reader["D_ZIP"].ToString(), reader["D_FAX"].ToString(), reader["D_EMAIL"].ToString(), reader["D_IS_WEEKED_WORK"].ToString(), reader["PRIORITY"].ToString(), reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["IS_ACTIVE"].ToString(), reader["REMARK"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime());
                }
            }
            return deliveryOrderInfo;
        }

        public DeliveryOrderInfo GetDeliveryOrderByID(DataBase dataBase, DbTransaction tran, string deliveryorderID, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_DELIVERY_ORDER.Replace("@_@", whLoginID) + " WHERE DELIVERY_ORDER_ID = @DELIVERY_ORDER_ID  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@DELIVERY_ORDER_ID", deliveryorderID), new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            DeliveryOrderInfo deliveryOrderInfo = null;
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql, paras);
            if (reader.Read())
            {
                DateTime CS00003;
                deliveryOrderInfo = new DeliveryOrderInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["DELIVERY_ORDER_ID"].ToString(), reader["EXTERN_ORDER_ID"].ToString(), reader["EXTERN_ORDER_ID2"].ToString(), reader["OWNER_ID"].ToString(), reader["DELIVERY_ORDER_TYPE"].ToString(), reader["START_LOC_ADDR"].ToString(), reader["END_LOC_ADDR"].ToString(), reader["DEPARTURE"].ToString(), reader["DESTINATION"].ToString(), (reader["TOTAL_QTY"].ToString() != "") ? decimal.Parse(reader["TOTAL_QTY"].ToString()) : 0M, (reader["TOTAL_WEIGHT"].ToString() != "") ? decimal.Parse(reader["TOTAL_WEIGHT"].ToString()) : 0M, (reader["TOTAL_CUBE"].ToString() != "") ? decimal.Parse(reader["TOTAL_CUBE"].ToString()) : 0M, (reader["REQUESTED_SHIP_DATE"].ToString() != "") ? DateTime.Parse(reader["REQUESTED_SHIP_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["ACTUAL_SHIP_DATE"].ToString() != "") ? DateTime.Parse(reader["ACTUAL_SHIP_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["EXPECT_ARRIVAL_DATE"].ToString() != "") ? DateTime.Parse(reader["EXPECT_ARRIVAL_DATE"].ToString()) : (CS00003 = new DateTime()), reader["STATUS"].ToString(), reader["TRANSFER_TYPE"].ToString(), reader["PICKUP_TYPE"].ToString(), reader["PAYMENT_TYPE"].ToString(), reader["BILLING_TYPE"].ToString(), reader["CARRIER_ID"].ToString(), reader["CUSTOMER_ID"].ToString(), reader["C_NAME"].ToString(), reader["C_CITY"].ToString(), reader["C_PROVINCE"].ToString(), reader["C_COUNTRY"].ToString(), reader["C_ZIP"].ToString(), reader["C_CONTACT1"].ToString(), reader["C_TEL1"].ToString(), reader["C_FAX1"].ToString(), reader["C_EMAIL1"].ToString(), reader["C_CONTACT2"].ToString(), reader["C_TEL2"].ToString(), reader["C_FAX2"].ToString(), reader["C_EMAIL2"].ToString(), reader["C_ADDRESS"].ToString(), reader["PICKUP_LOC_ID"].ToString(), reader["P_ADDRESS"].ToString(), reader["P_COUNTRY"].ToString(), reader["P_PROVINCE"].ToString(), reader["P_CITY"].ToString(), reader["P_CONTACT"].ToString(), reader["P_TEL"].ToString(), reader["P_ZIP"].ToString(), reader["P_FAX"].ToString(), reader["P_EMAIL"].ToString(), reader["P_IS_WEEKED_WORK"].ToString(), reader["DELIVERY_LOC_ID"].ToString(), reader["D_ADDRESS"].ToString(), reader["D_COUNTRY"].ToString(), reader["D_PROVINCE"].ToString(), reader["D_CITY"].ToString(), reader["D_CONTACT"].ToString(), reader["D_TEL"].ToString(), reader["D_ZIP"].ToString(), reader["D_FAX"].ToString(), reader["D_EMAIL"].ToString(), reader["D_IS_WEEKED_WORK"].ToString(), reader["PRIORITY"].ToString(), reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["IS_ACTIVE"].ToString(), reader["REMARK"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime());
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return deliveryOrderInfo;
        }

        public DataSet GetDeliveryOrderByQueryList(List<string> sqlWhere, DeliveryOrderQueryEntity deliveryorderQuery, string whLoginID, PubParasInfo pubParasInfo)
        {
            string temp = this.SQL_SELECT_ALL_DELIVERY_ORDER.Replace("@_@", whLoginID);
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            if (!deliveryorderQuery.IsGetAll)
            {
                temp = PagingHelper.GetPagingSQL(temp, deliveryorderQuery.CurrentPage, deliveryorderQuery.PageSize, deliveryorderQuery.SortField, deliveryorderQuery.SortDirection).Replace("@LANGUAGE", "''" + pubParasInfo.UserLanguage + "''").Replace("@USER_ID", "''" + pubParasInfo.UserID + "''");
            }
            else
            {
                temp = temp.Replace("@LANGUAGE", "'" + pubParasInfo.UserLanguage + "'").Replace("@USER_ID", "'" + pubParasInfo.UserID + "'");
            }
            return DBHelper.ExecuteDataSet(CommandType.Text, temp);
        }

        public DataSet GetDeliveryOrderByQueryList(DataBase dataBase, DbTransaction tran, List<string> sqlWhere, DeliveryOrderQueryEntity deliveryorderQuery, string whLoginID, PubParasInfo pubParasInfo)
        {
            string temp = this.SQL_SELECT_ALL_DELIVERY_ORDER.Replace("@_@", whLoginID);
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            if (deliveryorderQuery.IsGetAll)
            {
                temp = PagingHelper.GetPagingSQL(temp, deliveryorderQuery.CurrentPage, deliveryorderQuery.PageSize, deliveryorderQuery.SortField, deliveryorderQuery.SortDirection).Replace("@LANGUAGE", "''" + pubParasInfo.UserLanguage + "''").Replace("@USER_ID", "''" + pubParasInfo.UserID + "''");
            }
            else
            {
                temp = temp.Replace("@LANGUAGE", "'" + pubParasInfo.UserLanguage + "'").Replace("@USER_ID", "'" + pubParasInfo.UserID + "'");
            }
            return dataBase.ExecuteDataSet(tran, CommandType.Text, temp);
        }

        public int InsertDeliveryOrder(DeliveryOrderInfo deliveryOrderInfo, List<string> whLoginID)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    deliveryOrderInfo.WhID = whLoginID[i].Substring(0, whLoginID[i].Length - 1);
                    SqlParameter[] paras = this.Set_DeliveryOrder_Parameters(deliveryOrderInfo);
                    if (paras != null)
                    {
                        result += db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_DELIVERY_ORDER.Replace("@_@", whLoginID[i]), paras);
                    }
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int InsertDeliveryOrder(DataBase dataBase, DbTransaction tran, DeliveryOrderInfo deliveryOrderInfo, List<string> whLoginID)
        {
            int result = 0;
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    deliveryOrderInfo.WhID = whLoginID[i].Substring(0, whLoginID[i].Length - 1);
                    SqlParameter[] paras = this.Set_DeliveryOrder_Parameters(deliveryOrderInfo);
                    if (paras != null)
                    {
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_DELIVERY_ORDER.Replace("@_@", whLoginID[i]), paras);
                    }
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        private SqlParameter[] Set_DeliveryOrder_Parameters(DeliveryOrderInfo deliveryOrderInfo)
        {
            SqlParameter[] paramArray = new SqlParameter[] { 
                new SqlParameter(this.WH_ID, SqlDbType.VarChar, 30), new SqlParameter(this.DELIVERY_ORDER_ID, SqlDbType.VarChar, 30), new SqlParameter(this.EXTERN_ORDER_ID, SqlDbType.VarChar, 30), new SqlParameter(this.EXTERN_ORDER_ID2, SqlDbType.VarChar, 30), new SqlParameter(this.OWNER_ID, SqlDbType.VarChar, 30), new SqlParameter(this.DELIVERY_ORDER_TYPE, SqlDbType.VarChar, 30), new SqlParameter(this.START_LOC_ADDR, SqlDbType.VarChar, 30), new SqlParameter(this.END_LOC_ADDR, SqlDbType.VarChar, 30), new SqlParameter(this.DEPARTURE, SqlDbType.VarChar, 30), new SqlParameter(this.DESTINATION, SqlDbType.VarChar, 30), new SqlParameter(this.TOTAL_QTY, SqlDbType.Decimal, 13), new SqlParameter(this.TOTAL_WEIGHT, SqlDbType.Decimal, 13), new SqlParameter(this.TOTAL_CUBE, SqlDbType.Decimal, 13), new SqlParameter(this.REQUESTED_SHIP_DATE, SqlDbType.DateTime, 8), new SqlParameter(this.ACTUAL_SHIP_DATE, SqlDbType.DateTime, 8), new SqlParameter(this.EXPECT_ARRIVAL_DATE, SqlDbType.DateTime, 8), 
                new SqlParameter(this.STATUS, SqlDbType.VarChar, 30), new SqlParameter(this.TRANSFER_TYPE, SqlDbType.VarChar, 30), new SqlParameter(this.PICKUP_TYPE, SqlDbType.VarChar, 30), new SqlParameter(this.PAYMENT_TYPE, SqlDbType.VarChar, 30), new SqlParameter(this.BILLING_TYPE, SqlDbType.VarChar, 30), new SqlParameter(this.CARRIER_ID, SqlDbType.VarChar, 30), new SqlParameter(this.CUSTOMER_ID, SqlDbType.VarChar, 30), new SqlParameter(this.C_NAME, SqlDbType.VarChar, 30), new SqlParameter(this.C_CITY, SqlDbType.VarChar, 30), new SqlParameter(this.C_PROVINCE, SqlDbType.VarChar, 30), new SqlParameter(this.C_COUNTRY, SqlDbType.VarChar, 30), new SqlParameter(this.C_ZIP, SqlDbType.VarChar, 8), new SqlParameter(this.C_CONTACT1, SqlDbType.VarChar, 30), new SqlParameter(this.C_TEL1, SqlDbType.VarChar, 30), new SqlParameter(this.C_FAX1, SqlDbType.VarChar, 30), new SqlParameter(this.C_EMAIL1, SqlDbType.VarChar, 30), 
                new SqlParameter(this.C_CONTACT2, SqlDbType.VarChar, 30), new SqlParameter(this.C_TEL2, SqlDbType.VarChar, 30), new SqlParameter(this.C_FAX2, SqlDbType.VarChar, 30), new SqlParameter(this.C_EMAIL2, SqlDbType.VarChar, 30), new SqlParameter(this.C_ADDRESS, SqlDbType.VarChar, 100), new SqlParameter(this.PICKUP_LOC_ID, SqlDbType.VarChar, 30), new SqlParameter(this.P_ADDRESS, SqlDbType.VarChar, 100), new SqlParameter(this.P_COUNTRY, SqlDbType.VarChar, 30), new SqlParameter(this.P_PROVINCE, SqlDbType.VarChar, 30), new SqlParameter(this.P_CITY, SqlDbType.VarChar, 30), new SqlParameter(this.P_CONTACT, SqlDbType.VarChar, 30), new SqlParameter(this.P_TEL, SqlDbType.VarChar, 30), new SqlParameter(this.P_ZIP, SqlDbType.VarChar, 30), new SqlParameter(this.P_FAX, SqlDbType.VarChar, 30), new SqlParameter(this.P_EMAIL, SqlDbType.VarChar, 30), new SqlParameter(this.P_IS_WEEKED_WORK, SqlDbType.VarChar, 1), 
                new SqlParameter(this.DELIVERY_LOC_ID, SqlDbType.VarChar, 30), new SqlParameter(this.D_ADDRESS, SqlDbType.VarChar, 100), new SqlParameter(this.D_COUNTRY, SqlDbType.VarChar, 30), new SqlParameter(this.D_PROVINCE, SqlDbType.VarChar, 30), new SqlParameter(this.D_CITY, SqlDbType.VarChar, 30), new SqlParameter(this.D_CONTACT, SqlDbType.VarChar, 30), new SqlParameter(this.D_TEL, SqlDbType.VarChar, 30), new SqlParameter(this.D_ZIP, SqlDbType.VarChar, 30), new SqlParameter(this.D_FAX, SqlDbType.VarChar, 30), new SqlParameter(this.D_EMAIL, SqlDbType.VarChar, 30), new SqlParameter(this.D_IS_WEEKED_WORK, SqlDbType.VarChar, 1), new SqlParameter(this.PRIORITY, SqlDbType.VarChar, 1), new SqlParameter(this.UDF1, SqlDbType.VarChar, 30), new SqlParameter(this.UDF2, SqlDbType.VarChar, 30), new SqlParameter(this.UDF3, SqlDbType.VarChar, 30), new SqlParameter(this.UDF4, SqlDbType.VarChar, 30), 
                new SqlParameter(this.UDF5, SqlDbType.VarChar, 30), new SqlParameter(this.UDF6, SqlDbType.VarChar, 30), new SqlParameter(this.UDF7, SqlDbType.VarChar, 30), new SqlParameter(this.UDF8, SqlDbType.VarChar, 30), new SqlParameter(this.UDF9, SqlDbType.VarChar, 30), new SqlParameter(this.UDF10, SqlDbType.VarChar, 30), new SqlParameter(this.IS_ACTIVE, SqlDbType.VarChar, 30), new SqlParameter(this.REMARK, SqlDbType.VarChar, 100), new SqlParameter(this.CREATED_BY, SqlDbType.VarChar, 30), new SqlParameter(this.UPDATED_BY, SqlDbType.VarChar, 30)
             };
            if (!string.IsNullOrEmpty(deliveryOrderInfo.WhID))
            {
                paramArray[0].Value = deliveryOrderInfo.WhID;
            }
            else
            {
                paramArray[0].Value = DBNull.Value;
            }
            paramArray[1].Value = deliveryOrderInfo.DeliveryOrderID;
            if (!string.IsNullOrEmpty(deliveryOrderInfo.ExternOrderID))
            {
                paramArray[2].Value = deliveryOrderInfo.ExternOrderID;
            }
            else
            {
                paramArray[2].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(deliveryOrderInfo.ExternOrderId2))
            {
                paramArray[3].Value = deliveryOrderInfo.ExternOrderId2;
            }
            else
            {
                paramArray[3].Value = DBNull.Value;
            }
            paramArray[4].Value = deliveryOrderInfo.OwnerID;
            paramArray[5].Value = deliveryOrderInfo.DeliveryOrderType;
            paramArray[6].Value = deliveryOrderInfo.StartLocAddr;
            paramArray[7].Value = deliveryOrderInfo.EndLocAddr;
            paramArray[8].Value = deliveryOrderInfo.Departure;
            paramArray[9].Value = deliveryOrderInfo.Destination;
            paramArray[10].Value = deliveryOrderInfo.TotalQty;
            paramArray[11].Value = deliveryOrderInfo.TotalWeight;
            paramArray[12].Value = deliveryOrderInfo.TotalCube;
            paramArray[13].Value = deliveryOrderInfo.RequestedShipDate;
            paramArray[14].Value = deliveryOrderInfo.ActualShipDate;
            paramArray[15].Value = deliveryOrderInfo.ExpectArrivalDate;
            paramArray[0x10].Value = deliveryOrderInfo.Status;
            paramArray[0x11].Value = deliveryOrderInfo.TransferType;
            paramArray[0x12].Value = deliveryOrderInfo.PickupType;
            if (!string.IsNullOrEmpty(deliveryOrderInfo.PaymentType))
            {
                paramArray[0x13].Value = deliveryOrderInfo.PaymentType;
            }
            else
            {
                paramArray[0x13].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(deliveryOrderInfo.BillingType))
            {
                paramArray[20].Value = deliveryOrderInfo.BillingType;
            }
            else
            {
                paramArray[20].Value = DBNull.Value;
            }
            paramArray[0x15].Value = deliveryOrderInfo.CarrierID;
            paramArray[0x16].Value = deliveryOrderInfo.CustomerID;
            if (!string.IsNullOrEmpty(deliveryOrderInfo.CName))
            {
                paramArray[0x17].Value = deliveryOrderInfo.CName;
            }
            else
            {
                paramArray[0x17].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(deliveryOrderInfo.CCity))
            {
                paramArray[0x18].Value = deliveryOrderInfo.CCity;
            }
            else
            {
                paramArray[0x18].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(deliveryOrderInfo.CProvince))
            {
                paramArray[0x19].Value = deliveryOrderInfo.CProvince;
            }
            else
            {
                paramArray[0x19].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(deliveryOrderInfo.CCountry))
            {
                paramArray[0x1a].Value = deliveryOrderInfo.CCountry;
            }
            else
            {
                paramArray[0x1a].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(deliveryOrderInfo.CZip))
            {
                paramArray[0x1b].Value = deliveryOrderInfo.CZip;
            }
            else
            {
                paramArray[0x1b].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(deliveryOrderInfo.CContact1))
            {
                paramArray[0x1c].Value = deliveryOrderInfo.CContact1;
            }
            else
            {
                paramArray[0x1c].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(deliveryOrderInfo.CTel1))
            {
                paramArray[0x1d].Value = deliveryOrderInfo.CTel1;
            }
            else
            {
                paramArray[0x1d].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(deliveryOrderInfo.CFax1))
            {
                paramArray[30].Value = deliveryOrderInfo.CFax1;
            }
            else
            {
                paramArray[30].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(deliveryOrderInfo.CEmail1))
            {
                paramArray[0x1f].Value = deliveryOrderInfo.CEmail1;
            }
            else
            {
                paramArray[0x1f].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(deliveryOrderInfo.CContact2))
            {
                paramArray[0x20].Value = deliveryOrderInfo.CContact2;
            }
            else
            {
                paramArray[0x20].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(deliveryOrderInfo.CTel2))
            {
                paramArray[0x21].Value = deliveryOrderInfo.CTel2;
            }
            else
            {
                paramArray[0x21].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(deliveryOrderInfo.CFax2))
            {
                paramArray[0x22].Value = deliveryOrderInfo.CFax2;
            }
            else
            {
                paramArray[0x22].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(deliveryOrderInfo.CEmail2))
            {
                paramArray[0x23].Value = deliveryOrderInfo.CEmail2;
            }
            else
            {
                paramArray[0x23].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(deliveryOrderInfo.CAddress))
            {
                paramArray[0x24].Value = deliveryOrderInfo.CAddress;
            }
            else
            {
                paramArray[0x24].Value = DBNull.Value;
            }
            paramArray[0x25].Value = deliveryOrderInfo.PickupLocID;
            if (!string.IsNullOrEmpty(deliveryOrderInfo.PAddress))
            {
                paramArray[0x26].Value = deliveryOrderInfo.PAddress;
            }
            else
            {
                paramArray[0x26].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(deliveryOrderInfo.PCountry))
            {
                paramArray[0x27].Value = deliveryOrderInfo.PCountry;
            }
            else
            {
                paramArray[0x27].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(deliveryOrderInfo.PProvince))
            {
                paramArray[40].Value = deliveryOrderInfo.PProvince;
            }
            else
            {
                paramArray[40].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(deliveryOrderInfo.PCity))
            {
                paramArray[0x29].Value = deliveryOrderInfo.PCity;
            }
            else
            {
                paramArray[0x29].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(deliveryOrderInfo.PContact))
            {
                paramArray[0x2a].Value = deliveryOrderInfo.PContact;
            }
            else
            {
                paramArray[0x2a].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(deliveryOrderInfo.PTel))
            {
                paramArray[0x2b].Value = deliveryOrderInfo.PTel;
            }
            else
            {
                paramArray[0x2b].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(deliveryOrderInfo.PZip))
            {
                paramArray[0x2c].Value = deliveryOrderInfo.PZip;
            }
            else
            {
                paramArray[0x2c].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(deliveryOrderInfo.PFax))
            {
                paramArray[0x2d].Value = deliveryOrderInfo.PFax;
            }
            else
            {
                paramArray[0x2d].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(deliveryOrderInfo.PEmail))
            {
                paramArray[0x2e].Value = deliveryOrderInfo.PEmail;
            }
            else
            {
                paramArray[0x2e].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(deliveryOrderInfo.PIsWeekedWork))
            {
                paramArray[0x2f].Value = deliveryOrderInfo.PIsWeekedWork;
            }
            else
            {
                paramArray[0x2f].Value = DBNull.Value;
            }
            paramArray[0x30].Value = deliveryOrderInfo.DeliveryLocID;
            if (!string.IsNullOrEmpty(deliveryOrderInfo.DAddress))
            {
                paramArray[0x31].Value = deliveryOrderInfo.DAddress;
            }
            else
            {
                paramArray[0x31].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(deliveryOrderInfo.DCountry))
            {
                paramArray[50].Value = deliveryOrderInfo.DCountry;
            }
            else
            {
                paramArray[50].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(deliveryOrderInfo.DProvince))
            {
                paramArray[0x33].Value = deliveryOrderInfo.DProvince;
            }
            else
            {
                paramArray[0x33].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(deliveryOrderInfo.DCity))
            {
                paramArray[0x34].Value = deliveryOrderInfo.DCity;
            }
            else
            {
                paramArray[0x34].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(deliveryOrderInfo.DContact))
            {
                paramArray[0x35].Value = deliveryOrderInfo.DContact;
            }
            else
            {
                paramArray[0x35].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(deliveryOrderInfo.DTel))
            {
                paramArray[0x36].Value = deliveryOrderInfo.DTel;
            }
            else
            {
                paramArray[0x36].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(deliveryOrderInfo.DZip))
            {
                paramArray[0x37].Value = deliveryOrderInfo.DZip;
            }
            else
            {
                paramArray[0x37].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(deliveryOrderInfo.DFax))
            {
                paramArray[0x38].Value = deliveryOrderInfo.DFax;
            }
            else
            {
                paramArray[0x38].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(deliveryOrderInfo.DEmail))
            {
                paramArray[0x39].Value = deliveryOrderInfo.DEmail;
            }
            else
            {
                paramArray[0x39].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(deliveryOrderInfo.DIsWeekedWork))
            {
                paramArray[0x3a].Value = deliveryOrderInfo.DIsWeekedWork;
            }
            else
            {
                paramArray[0x3a].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(deliveryOrderInfo.Priority))
            {
                paramArray[0x3b].Value = deliveryOrderInfo.Priority;
            }
            else
            {
                paramArray[0x3b].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(deliveryOrderInfo.Udf1))
            {
                paramArray[60].Value = deliveryOrderInfo.Udf1;
            }
            else
            {
                paramArray[60].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(deliveryOrderInfo.Udf2))
            {
                paramArray[0x3d].Value = deliveryOrderInfo.Udf2;
            }
            else
            {
                paramArray[0x3d].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(deliveryOrderInfo.Udf3))
            {
                paramArray[0x3e].Value = deliveryOrderInfo.Udf3;
            }
            else
            {
                paramArray[0x3e].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(deliveryOrderInfo.Udf4))
            {
                paramArray[0x3f].Value = deliveryOrderInfo.Udf4;
            }
            else
            {
                paramArray[0x3f].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(deliveryOrderInfo.Udf5))
            {
                paramArray[0x40].Value = deliveryOrderInfo.Udf5;
            }
            else
            {
                paramArray[0x40].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(deliveryOrderInfo.Udf6))
            {
                paramArray[0x41].Value = deliveryOrderInfo.Udf6;
            }
            else
            {
                paramArray[0x41].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(deliveryOrderInfo.Udf7))
            {
                paramArray[0x42].Value = deliveryOrderInfo.Udf7;
            }
            else
            {
                paramArray[0x42].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(deliveryOrderInfo.Udf8))
            {
                paramArray[0x43].Value = deliveryOrderInfo.Udf8;
            }
            else
            {
                paramArray[0x43].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(deliveryOrderInfo.Udf9))
            {
                paramArray[0x44].Value = deliveryOrderInfo.Udf9;
            }
            else
            {
                paramArray[0x44].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(deliveryOrderInfo.Udf10))
            {
                paramArray[0x45].Value = deliveryOrderInfo.Udf10;
            }
            else
            {
                paramArray[0x45].Value = DBNull.Value;
            }
            paramArray[70].Value = deliveryOrderInfo.IsActive;
            if (!string.IsNullOrEmpty(deliveryOrderInfo.Remark))
            {
                paramArray[0x47].Value = deliveryOrderInfo.Remark;
            }
            else
            {
                paramArray[0x47].Value = DBNull.Value;
            }
            paramArray[0x48].Value = deliveryOrderInfo.CreatedBy;
            paramArray[0x49].Value = deliveryOrderInfo.UpdatedBy;
            return paramArray;
        }

        public int UpdateDeliveryOrder(DeliveryOrderInfo deliveryOrderInfo, List<string> whLoginID)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    deliveryOrderInfo.WhID = whLoginID[i].Substring(0, whLoginID[i].Length - 1);
                    SqlParameter[] paras = this.Set_DeliveryOrder_Parameters(deliveryOrderInfo);
                    if (paras != null)
                    {
                        result += db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_DELIVERY_ORDER.Replace("@_@", whLoginID[i]), paras);
                    }
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int UpdateDeliveryOrder(DataBase dataBase, DbTransaction tran, DeliveryOrderInfo deliveryOrderInfo, List<string> whLoginID)
        {
            int result = 0;
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    deliveryOrderInfo.WhID = whLoginID[i].Substring(0, whLoginID[i].Length - 1);
                    SqlParameter[] paras = this.Set_DeliveryOrder_Parameters(deliveryOrderInfo);
                    if (paras != null)
                    {
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_DELIVERY_ORDER.Replace("@_@", whLoginID[i]), paras);
                    }
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }
    }
}

